package com.fertshop.dao;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.fertshop.dto.ShopDto;
import com.fertshop.exception.NoRecordException;
import com.fertshop.exception.SPDoorNoExistException;
import com.fertshop.exception.StrgpDoorNumberExistException;
import com.fertshop.gui.Main;

public class FertishopDao {
	
	/**
	 * This method checks whether the shop door number exist. 
	 * If door number exist it throws warning. if door number does
	 * not exist it stores the shop data.
	 * 
	 * @param shopdto
	 */
	public void addShop(ShopDto shopdto)throws Exception{
		checkSalepointDoorNoExistance(shopdto);
		checkStoragepointDoorNoExistance(shopdto);
		addShopDetails(shopdto);
		int shoppk=getShopPrimaryKey();
		storeSalePoint(shoppk,shopdto);
		storeStoragePoint(shoppk,shopdto);
		
	}
	
	/**
	 * This method returns the Connection.
	 * 
	 * @return Connection.
	 */
	private Connection getconnection(){
		Connection conn=null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn =DriverManager.getConnection("jdbc:mysql://localhost/fertishop?" +
				                                   "user=root&password=yaragalla");
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return conn;
	}
	
	/**
	 * This method adds the shop details that are entered in the GUI.
	 * 
	 * @param shopdto
	 */
	public void addShopDetails(ShopDto shopdto){
		Connection conn=null;
		PreparedStatement pstmt=null;
		String sql="INSERT INTO shops (shop_name,propraitor_name," +
		    		"license_number,address) VALUES (?,?,?,?)";
		try{
			
			conn =getconnection();
			 pstmt = conn.prepareStatement(sql);
			 
			 pstmt.setString(1, shopdto.getShopName());
			 pstmt.setString(2, shopdto.getProprietorName());
			 pstmt.setString(3, shopdto.getLicenseNumber());
			 pstmt.setString(4, shopdto.getAddress());
			 
			 pstmt.executeUpdate();
			 
		}catch(Exception e){
			e.printStackTrace();
		} finally {
		      try {
		          pstmt.close();
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}
	
	/**
	 * This method returns the shops primary key.
	 * 
	 * @param shopdto
	 * @return primarykey.
	 */
	public int getShopPrimaryKey(){
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		int shoppk=0;
		String sql="select MAX(shop_id) as shop_id from shops";
		try{
			
			conn =getconnection();
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				shoppk=rs.getInt("shop_id");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			      try {
			    	  rs.close();			     
			          stmt.close();			          
			          conn.close();
			      } catch (SQLException e) {
			          e.printStackTrace();
			        }
			  }
		return shoppk;
	}
	
	/**
	 * This method stores sale point details.
	 * 
	 * @param shoppk
	 * @param shopdto
	 */
	public void storeSalePoint(int shoppk,ShopDto shopdto){
		String compareDoorNo=removeSpaces(shopdto.getSpDoorNo());
		Connection conn=null;
		PreparedStatement pstmt=null;
		String sql="INSERT INTO salepoint (shop_id,door_number," +
		    		"village,compare_string) VALUES (?,?,?,?)";
		try{
			
			conn =getconnection();
			 pstmt = conn.prepareStatement(sql);
			 
			 pstmt.setInt(1, shoppk);
			 pstmt.setString(2, shopdto.getSpDoorNo());
			 pstmt.setString(3, shopdto.getSpArea());
			 pstmt.setString(4, compareDoorNo);
			 
			 pstmt.executeUpdate();
			 
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
		
	}
	
	/**
	 * This method stores storage point details.
	 * 
	 * @param shoppk
	 * @param shopdto
	 */
	public void storeStoragePoint(int shoppk,ShopDto shopdto){
		String compareDoorNo=removeSpaces(shopdto.getStrgpDoorNo());
		Connection conn=null;
		PreparedStatement pstmt=null;
		String sql="INSERT INTO storagepoint (shop_id,door_number," +
		    		"village,compare_string) VALUES (?,?,?,?)";
		try{
			
			conn =getconnection();
			pstmt = conn.prepareStatement(sql);
			 
			 pstmt.setInt(1, shoppk);
			 if(shopdto.getStrgpDoorNo().isEmpty()){
				 pstmt.setString(2, null);
			 }else{
				 pstmt.setString(2, shopdto.getStrgpDoorNo());
			 }
			 if(shopdto.getStrgpArea().isEmpty()){
				 pstmt.setString(3, null);
			 }else{
				 pstmt.setString(3, shopdto.getStrgpArea());
			 }
			 if(compareDoorNo.isEmpty()){
				 pstmt.setString(4, null);
			 }else{
				 pstmt.setString(4, compareDoorNo);
			 }
			 
			 pstmt.executeUpdate();
			 
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
		
	}
	
	/**
	 * This method removes the spaces in the given string.
	 * @param str
	 * @return string.
	 */
	private String removeSpaces(String str){
		
		StringBuffer finalstrbfr=new StringBuffer();		
		StringReader sr=new StringReader(str);
		try{
			for(int i=0;i<str.length();i++){
				char kar=(char)sr.read();
				if(kar != ' '){
					finalstrbfr.append(kar);
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return finalstrbfr.toString();
	}
	
	/**
	 * This method checks whether sale point door number already exist.
	 * @param shopdto
	 * @throws Exception
	 */
	public void checkSalepointDoorNoExistance(ShopDto shopdto)throws Exception{
		String compareSpDoorNo=removeSpaces(shopdto.getSpDoorNo());
		String sql="select a.shop_name,a.propraitor_name,a.license_number," +
				"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
				"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
				"b.shop_id=c.shop_id and ((b.compare_string = ? and b.village = ?) or " +
				"(c.compare_string = ? and c.village = ?))";
		Connection conn =getconnection();
		PreparedStatement pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, compareSpDoorNo);
		pstmt.setString(2, shopdto.getSpArea());
		pstmt.setString(3, compareSpDoorNo);
		pstmt.setString(4, shopdto.getSpArea());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			ShopDto _shopdto=new ShopDto();
			_shopdto.setShopName(rs.getString("shop_name"));
			_shopdto.setProprietorName(rs.getString("propraitor_name"));
			_shopdto.setLicenseNumber(rs.getString("license_number"));
			_shopdto.setAddress(rs.getString("address"));
			_shopdto.setSpDoorNo(rs.getString("door_number"));
			_shopdto.setSpArea(rs.getString("village"));
			_shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			_shopdto.setStrgpArea(rs.getString("strgVillage"));
			Main.shopdto=_shopdto;
			 try {
		    	  rs.close();
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
			throw new SPDoorNoExistException("The Sale Point Door Number Already Exist.");
		}	     
		 
	}
	
	/**
	 * This method checks whether storage point door number already exist.
	 * @param shopdto
	 * @throws Exception
	 */
	public void checkStoragepointDoorNoExistance(ShopDto shopdto)throws Exception{
		String compareStrgpDoorNo=removeSpaces(shopdto.getStrgpDoorNo());
		String sql="select a.shop_name,a.propraitor_name,a.license_number," +
				"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
				"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
				"b.shop_id=c.shop_id and ((b.compare_string = ? and b.village = ?) or " +
				"(c.compare_string = ? and c.village = ?))";
		Connection conn =getconnection();
		PreparedStatement pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, compareStrgpDoorNo);
		pstmt.setString(2, shopdto.getStrgpArea());
		pstmt.setString(3, compareStrgpDoorNo);
		pstmt.setString(4, shopdto.getStrgpArea());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			ShopDto _shopdto=new ShopDto();
			_shopdto.setShopName(rs.getString("shop_name"));
			_shopdto.setProprietorName(rs.getString("propraitor_name"));
			_shopdto.setLicenseNumber(rs.getString("license_number"));
			_shopdto.setAddress(rs.getString("address"));
			_shopdto.setSpDoorNo(rs.getString("door_number"));
			_shopdto.setSpArea(rs.getString("village"));
			_shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			_shopdto.setStrgpArea(rs.getString("strgVillage"));
			Main.shopdto=_shopdto;
			try {
		    	  rs.close();
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
			throw new StrgpDoorNumberExistException("The Storage Point Door Number Already Exist.");
		}
		
		
	      
		 
	}
	
	/**
	 * This method searches the shop when given salepoint door number and village.
	 * @param salepointDoorNo
	 * @param salepointVillage
	 * @return
	 * @throws Exception
	 */
	public ShopDto searchShop(String salepointDoorNo, String salepointVillage)throws Exception{
		ShopDto _shopdto=null;
		String compareStrgpDoorNo=removeSpaces(salepointDoorNo);
		String sql="select a.shop_id, a.shop_name,a.propraitor_name,a.license_number,a.address," +
				"b.salepoint_id,b.door_number,b.village,c.storagepoint_id,c.door_number as strgDoorNo," +
				"c.village as strgVillage from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id" +
				" and b.shop_id=c.shop_id and b.compare_string=? and b.village=?";
		Connection conn=getconnection();
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, compareStrgpDoorNo);
		pstmt.setString(2, salepointVillage);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			_shopdto=new ShopDto();
			_shopdto.setShopidpk(rs.getInt("shop_id"));
			_shopdto.setShopName(rs.getString("shop_name"));
			_shopdto.setProprietorName(rs.getString("propraitor_name"));
			_shopdto.setLicenseNumber(rs.getString("license_number"));
			_shopdto.setAddress(rs.getString("address"));
			_shopdto.setSpidpk(rs.getInt("salepoint_id"));
			_shopdto.setSpDoorNo(rs.getString("door_number"));
			_shopdto.setSpArea(rs.getString("village"));
			_shopdto.setStrgpidpk(rs.getInt("storagepoint_id"));
			_shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			_shopdto.setStrgpArea(rs.getString("strgVillage"));
		}else{
			try {
		    	  rs.close();
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
			throw new NoRecordException("No Record Found");
		}
		
		try {
	    	  rs.close();
	    	  pstmt.close();			          
	          conn.close();
	      } catch (SQLException e) {
	          e.printStackTrace();
	        }
		
		return _shopdto;
	}
	
	
	
	public void updateShop(ShopDto shopdto)throws Exception{
		checkUpdateSalepointDoorNoExistance(shopdto);		
		checkUpdateStoragepointDoorNoExistance(shopdto);
		updateShopDetails(shopdto);
		updateSalepoint(shopdto);
		updateStoragepoint(shopdto);
	}
	
	/**
	 * This method checks whether sale point door number already exist for update.
	 * @param shopdto
	 * @throws Exception
	 */
	public void checkUpdateSalepointDoorNoExistance(ShopDto shopdto)throws Exception{
		String compareSpDoorNo=removeSpaces(shopdto.getSpDoorNo());
		String sql="select a.shop_name,a.propraitor_name,a.license_number," +
				"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
				"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
				"b.shop_id=c.shop_id and ((b.compare_string = ? and b.village = ?) or " +
				"(c.compare_string = ? and c.village = ?)) and b.salepoint_id<>?";
		Connection conn =getconnection();
		PreparedStatement pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, compareSpDoorNo);
		pstmt.setString(2, shopdto.getSpArea());
		pstmt.setString(3, compareSpDoorNo);
		pstmt.setString(4, shopdto.getSpArea());
		pstmt.setInt(5, Main.updatedto.getSpidpk());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			ShopDto _shopdto=new ShopDto();
			_shopdto.setShopName(rs.getString("shop_name"));
			_shopdto.setProprietorName(rs.getString("propraitor_name"));
			_shopdto.setLicenseNumber(rs.getString("license_number"));
			_shopdto.setAddress(rs.getString("address"));
			_shopdto.setSpDoorNo(rs.getString("door_number"));
			_shopdto.setSpArea(rs.getString("village"));
			_shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			_shopdto.setStrgpArea(rs.getString("strgVillage"));
			Main.shopdto=_shopdto;
			 try {
		    	  rs.close();
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
			throw new SPDoorNoExistException("The Sale Point Door Number Already Exist.");
		}	     
		 
	}

	
	/**
	 * This method checks whether storage point door number already exist.
	 * @param shopdto
	 * @throws Exception
	 */
	public void checkUpdateStoragepointDoorNoExistance(ShopDto shopdto)throws Exception{
		String compareStrgpDoorNo=removeSpaces(shopdto.getStrgpDoorNo());
		String sql="select a.shop_name,a.propraitor_name,a.license_number," +
				"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
				"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
				"b.shop_id=c.shop_id and ((b.compare_string = ? and b.village = ?) or " +
				"(c.compare_string = ? and c.village = ?)) and c.storagepoint_id<>?";
		Connection conn =getconnection();
		PreparedStatement pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, compareStrgpDoorNo);
		pstmt.setString(2, shopdto.getStrgpArea());
		pstmt.setString(3, compareStrgpDoorNo);
		pstmt.setString(4, shopdto.getStrgpArea());
		pstmt.setInt(5, Main.updatedto.getStrgpidpk());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			ShopDto _shopdto=new ShopDto();
			_shopdto.setShopName(rs.getString("shop_name"));
			_shopdto.setProprietorName(rs.getString("propraitor_name"));
			_shopdto.setLicenseNumber(rs.getString("license_number"));
			_shopdto.setAddress(rs.getString("address"));
			_shopdto.setSpDoorNo(rs.getString("door_number"));
			_shopdto.setSpArea(rs.getString("village"));
			_shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			_shopdto.setStrgpArea(rs.getString("strgVillage"));
			Main.shopdto=_shopdto;
			try {
		    	  rs.close();
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
			throw new StrgpDoorNumberExistException("The Storage Point Door Number Already Exist.");
		}
		
		
	      
		 
	}
	
	public void updateShopDetails(ShopDto shopdto){
		
		String sql="update shops set shop_name=?,propraitor_name=?," +
				"license_number=?,address=? where shop_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			
			 pstmt.setString(1, shopdto.getShopName());
			 pstmt.setString(2, shopdto.getProprietorName());
			 pstmt.setString(3, shopdto.getLicenseNumber());
			 pstmt.setString(4, shopdto.getAddress());
			 pstmt.setInt(5, Main.updatedto.getShopidpk());			 
			 
			 pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}
	
	
public void updateSalepoint(ShopDto shopdto){
	String compareSpDoorNo=removeSpaces(shopdto.getSpDoorNo());
		String sql="update salepoint set door_number=?,village=?," +
				"compare_string=? where salepoint_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			
			 pstmt.setString(1, shopdto.getSpDoorNo());
			 pstmt.setString(2, shopdto.getSpArea());
			 pstmt.setString(3, compareSpDoorNo);			
			 pstmt.setInt(4, Main.updatedto.getSpidpk());			 
			 
			 pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}

public void updateStoragepoint(ShopDto shopdto){
	String compareStrgpDoorNo=removeSpaces(shopdto.getStrgpDoorNo());
		String sql="update storagepoint set door_number=?,village=?," +
				"compare_string=? where storagepoint_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			 if(shopdto.getStrgpDoorNo().isEmpty()){
				 pstmt.setString(1, null); 
			 }else{
				 pstmt.setString(1, shopdto.getStrgpDoorNo());
			 }
			 if(shopdto.getStrgpArea().isEmpty()){
				 pstmt.setString(2, null); 
			 }else{
				 pstmt.setString(2, shopdto.getStrgpArea());
			 }
			 if(compareStrgpDoorNo.isEmpty()){
				 pstmt.setString(3, null);
			 }else{
				 pstmt.setString(3, compareStrgpDoorNo);
			 }
			 pstmt.setInt(4, Main.updatedto.getStrgpidpk());			 
			 
			 pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}

public void deleteShop(String salepointDoorNo, String salepointVillage)throws Exception{
	 ShopDto shopdto=searchShop(salepointDoorNo, salepointVillage);
	 deleteSalepoint(shopdto);
	 deleteStoragepoint(shopdto);
	 deleteShopDetailst(shopdto);
}

/**
 * This method deletes the salepoint record from the salepoint table.
 * @param shopdto
 */
public void deleteSalepoint(ShopDto shopdto){
	
		String sql="delete from salepoint where salepoint_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setInt(1, shopdto.getSpidpk());
			
			pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}

/**
 * This method deletes the storagepoint record from the storagepoint table.
 * @param shopdto
 */
public void deleteStoragepoint(ShopDto shopdto){
	
		String sql="delete from storagepoint where storagepoint_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setInt(1, shopdto.getStrgpidpk());
			
			pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}

/**
 * This method deletes the shops record from the shops table.
 * @param shopdto
 */
public void deleteShopDetailst(ShopDto shopdto){
	
		String sql="delete from shops where shop_id=?";
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=getconnection();
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setInt(1, shopdto.getShopidpk());
			
			pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
		      try {
		    	  pstmt.close();			          
		          conn.close();
		      } catch (SQLException e) {
		          e.printStackTrace();
		        }
		  }
	}

public ArrayList<ShopDto> getAllShopsByShopName(){
	ArrayList<ShopDto> shopsList=new ArrayList<ShopDto>();
	String sql="select a.shop_name,a.propraitor_name,a.license_number," +
			"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
			"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
			"b.shop_id=c.shop_id order by a.shop_name";
	Connection conn=null;
	Statement stmt=null;
	ResultSet rs=null;	
	try{
		conn=getconnection();
		stmt=conn.createStatement();		
		rs=stmt.executeQuery(sql);	
		while(rs.next()){
			ShopDto shopdto=new ShopDto();
			shopdto.setShopName(rs.getString("shop_name"));
			shopdto.setProprietorName(rs.getString("propraitor_name"));
			shopdto.setLicenseNumber(rs.getString("license_number"));
			shopdto.setAddress(rs.getString("address"));
			shopdto.setSpDoorNo(rs.getString("door_number"));
			shopdto.setSpArea(rs.getString("village"));
			shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			shopdto.setStrgpArea(rs.getString("strgVillage"));
			shopsList.add(shopdto);
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally {
	      try {
	    	  stmt.close();			          
	          conn.close();
	      } catch (SQLException e) {
	          e.printStackTrace();
	        }
	  }
	return shopsList;
}

public ArrayList<ShopDto> getAllShopsBySalepointArea(){
	ArrayList<ShopDto> shopsList=new ArrayList<ShopDto>();
	String sql="select a.shop_name,a.propraitor_name,a.license_number," +
			"a.address,b.door_number,b.village,c.door_number as strgDoorNo,c.village as strgVillage " +
			"from shops a,salepoint b,storagepoint c where a.shop_id=b.shop_id and " +
			"b.shop_id=c.shop_id order by b.village, a.shop_name";
	Connection conn=null;
	Statement stmt=null;
	ResultSet rs=null;	
	try{
		conn=getconnection();
		stmt=conn.createStatement();		
		rs=stmt.executeQuery(sql);	
		while(rs.next()){
			ShopDto shopdto=new ShopDto();
			shopdto.setShopName(rs.getString("shop_name"));
			shopdto.setProprietorName(rs.getString("propraitor_name"));
			shopdto.setLicenseNumber(rs.getString("license_number"));
			shopdto.setAddress(rs.getString("address"));
			shopdto.setSpDoorNo(rs.getString("door_number"));
			shopdto.setSpArea(rs.getString("village"));
			shopdto.setStrgpDoorNo(rs.getString("strgDoorNo"));
			shopdto.setStrgpArea(rs.getString("strgVillage"));
			shopsList.add(shopdto);
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally {
	      try {
	    	  stmt.close();			          
	          conn.close();
	      } catch (SQLException e) {
	          e.printStackTrace();
	        }
	  }
	return shopsList;
}


}
